﻿using System;
using System.ComponentModel;
using System.Data.SqlClient;
using QLHS.DTO;

namespace QLHS.DataLayer
{
    public class HocSinhDAO
    {
        public BindingList<HocSinhDTO> LoadAll()
        {
            SqlConnection cn = new SqlConnection(Helper.ConnectionString);
            string sql = "select * from HOCSINH";
            BindingList<HocSinhDTO> HocSinhTable = new BindingList<HocSinhDTO>();
            
            try
            {
                SqlCommand cmd = new SqlCommand(sql, cn);
                cn.Open();
                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        string _MSHS = reader["MaSoHS"].ToString();
                        string _TenHS = reader["TenHS"].ToString();
                        DateTime _NgaySinh = Convert.ToDateTime(reader["NgaySinh"].ToString());
                        string _GioiTinh = reader["GioiTinh"].ToString();
                        string _Email = reader["Email"].ToString();
                        string _DiaChi = reader["DiaChi"].ToString();
                        int _MSLop = Convert.ToInt32(reader["MaSoLop"].ToString());
                        int _ID = Convert.ToInt32(reader["ID"].ToString());
                        HocSinhDTO temp = new HocSinhDTO
                        {
                            MaSoHS = _MSHS,
                            TenHS = _TenHS,
                            NgaySinh = _NgaySinh,
                            GioiTinh = _GioiTinh,
                            Email = _Email,
                            DiaChi = _DiaChi,
                            MaSoLop = _MSLop,
                            ID = _ID
                        };
                        HocSinhTable.Add(temp);
                    }
                }
            }
            finally
            {
                if (cn != null && cn.State == System.Data.ConnectionState.Open)
                {
                    cn.Close();
                }
            }

            return HocSinhTable;
        }

        public void Delete(HocSinhDTO del)
        {
            string sql = string.Format("delete from HocSinh where ID = {0}", del.ID);
            SqlConnection cn = new SqlConnection(Helper.ConnectionString);

            try
            {
                SqlCommand cmd = new SqlCommand(sql, cn);
                cn.Open();
                cmd.ExecuteNonQuery();
            }
            finally
            {
                if (cn != null && cn.State == System.Data.ConnectionState.Open)
                {
                    cn.Close();
                }
            }
        }

        public void Insert(HocSinhDTO InsertObject)
        {
            string sql;
            if (InsertObject.MaSoLop.HasValue)
            {
                sql = string.Format("insert into HOCSINH values ('{0}', N'{1}', '{2}', N'{3}', N'{4}', N'{5}', {6})",
                   InsertObject.MaSoHS,
                   InsertObject.TenHS,
                   InsertObject.NgaySinh,
                   InsertObject.GioiTinh,
                   InsertObject.DiaChi,
                   InsertObject.Email,
                   InsertObject.MaSoLop);
            }
            else
            {
                sql = string.Format("insert into HOCSINH values ('{0}', N'{1}', '{2}', N'{3}', N'{4}', N'{5}', NULL)",
                    InsertObject.MaSoHS, 
                    InsertObject.TenHS, 
                    InsertObject.NgaySinh, 
                    InsertObject.GioiTinh, 
                    InsertObject.DiaChi, 
                    InsertObject.Email);
            }
            SqlConnection cn = new SqlConnection(Helper.ConnectionString);

            try
            {
                SqlCommand cmd = new SqlCommand(sql, cn);
                cn.Open();
                cmd.ExecuteNonQuery();
            }
            finally
            {
                if (cn != null && cn.State == System.Data.ConnectionState.Open)
                {
                    cn.Close();
                }
            }
        }

        public void Update(HocSinhDTO UpdateObject)
        {
            string sql;
            if (UpdateObject.MaSoLop.HasValue)
            {
                sql = string.Format("update HOCSINH set MaSoHS = '{0}', TenHS = N'{1}' , NgaySinh = '{2}' , GioiTinh = N'{3}' , DiaChi = N'{4}', Email = N'{5}' , MaSoLop = {6} where ID = {7}",
                UpdateObject.MaSoHS, 
                UpdateObject.TenHS, 
                UpdateObject.NgaySinh, 
                UpdateObject.GioiTinh, 
                UpdateObject.DiaChi, 
                UpdateObject.Email, 
                UpdateObject.MaSoLop, 
                UpdateObject.ID);
            }
            else
            {
                sql = string.Format(
                    "update HOCSINH set MaSoHS = '{0}', TenHS = N'{1}' , NgaySinh = '{2}' , GioiTinh = N'{3}' , DiaChi = N'{4}', Email = N'{5}' where ID = {6}",
                    UpdateObject.MaSoHS,
                    UpdateObject.TenHS,
                    UpdateObject.NgaySinh,
                    UpdateObject.GioiTinh,
                    UpdateObject.DiaChi,
                    UpdateObject.Email,
                    UpdateObject.ID);
            }
            SqlConnection cn = new SqlConnection(Helper.ConnectionString);

            try
            {
                SqlCommand cmd = new SqlCommand(sql, cn);
                cn.Open();
                cmd.ExecuteNonQuery();
            }
            finally
            {
                if (cn != null && cn.State == System.Data.ConnectionState.Open)
                {
                    cn.Close();
                }
            }
        }

        //public static void ChangeKeyValue(string Old_MSHS, string New_MSHS)
        //{
        //    string sql = string.Format("update HocSinh set MSHS = '{0}' where (MSHS = '{1}')", New_MSHS, Old_MSHS);
        //    SqlConnection Cn = new SqlConnection(Helper.ConnectionString);

        //    try
        //    {
        //        SqlCommand Cmd = new SqlCommand(sql, Cn);
        //        Cn.Open();
        //        Cmd.ExecuteNonQuery();
        //    }
        //    finally
        //    {
        //        if (Cn != null && Cn.State == System.Data.ConnectionState.Open)
        //        {
        //            Cn.Close();
        //        }
        //    }
        //}

        public BindingList<HocSinhDTO> GetHocSinhDataByPrimaryKey(int ID)
        {
            SqlConnection cn = new SqlConnection(Helper.ConnectionString);
            string sql = string.Format("select * from HOCSINH where ID = {0}", ID);
            BindingList<HocSinhDTO> Result = new BindingList<HocSinhDTO>();

            try
            {
                SqlCommand cmd = new SqlCommand(sql, cn);
                cn.Open();
                SqlDataReader Reader = cmd.ExecuteReader();
                using (var reader = cmd.ExecuteReader())
                {
                    while (Reader.Read())
                    {
                        string _MSHS = reader["MaSoHS"].ToString();
                        string _TenHS = reader["TenHS"].ToString();
                        DateTime _NgaySinh = Convert.ToDateTime(reader["NgaySinh"].ToString());
                        string _GioiTinh = reader["GioiTinh"].ToString();
                        string _Email = reader["Email"].ToString();
                        string _DiaChi = reader["DiaChi"].ToString();
                        int _MSLop = Convert.ToInt32(reader["MaSoLop"].ToString());
                        int _ID = Convert.ToInt32(reader["ID"].ToString());
                        HocSinhDTO temp = new HocSinhDTO
                        {
                            MaSoHS = _MSHS,
                            TenHS = _TenHS,
                            NgaySinh = _NgaySinh,
                            GioiTinh = _GioiTinh,
                            Email = _Email,
                            DiaChi = _DiaChi,
                            MaSoLop = _MSLop,
                            ID = _ID
                        };
                        Result.Add(temp);
                    }
                }
            }
            finally
            {
                if (cn != null && cn.State == System.Data.ConnectionState.Open)
                {
                    cn.Close();
                }
            }

            return Result;
        }
    }
}
